package org.sfgeeq.chapter2.service;

import org.sfgeeq.chapter2.helper.DatabaseHelper;
import org.sfgeeq.chapter2.helper.DatabaseHelperThreadLocal;
import org.sfgeeq.chapter2.model.Customer;
import org.sfgeeq.chapter2.util.PropsUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;

/**
 * 提供客户数据服务 压标厅
 * Created by  on 2016/8/25.
 */
public class CustomerService {
    private static final Logger log= LoggerFactory.getLogger(CustomerService.class);
    private static final String DRIVER;
    private static final String URL;
    private static final String USERNAME;
    private static final String PASSWORD;
    static {
        Properties conf= PropsUtil.loadProps("config.properties");
        DRIVER=conf.getProperty("jdbc.driver");
        URL=conf.getProperty("jdbc.url");
        USERNAME=conf.getProperty("jdbc.username");
        PASSWORD=conf.getProperty("jdbc.password");
        try{
            Class.forName(DRIVER);
        }catch (ClassNotFoundException e){
            log.error("can not load jbdc driver(无法加载jdcb)",e);
        }
    }
    /**
     * 获取客户列表 方法1
     */
    public List<Customer> getCustomerListOne(String id){
        Connection conn=null;
        String sql="SELECT * FROM customer";
        List<Customer> list =new ArrayList<Customer>();
        try{
            conn= DriverManager.getConnection(URL,USERNAME,PASSWORD);
            PreparedStatement stmt=conn.prepareStatement(sql);
            ResultSet rs=stmt.executeQuery();
            while (rs.next()){
                Customer c=new Customer();
                c.setId(rs.getLong("id"));
                c.setName(rs.getString("name"));
                c.setContact(rs.getString("contact"));
                c.setTelephone(rs.getString("telephone"));
                c.setEmail(rs.getString("email"));
                c.setRemark(rs.getString("remark"));

                list.add(c);
            }
        }catch (SQLException e){
            log.error("执行sql失败，SQL为："+sql,e);
        }finally {
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }
    /**
     * 获取客户列表 方法2
     * 将打开和关闭数据库连接的代码抽取到公共方法里了。
     */
    public List<Customer> getCustomerListTwo(String id){
        Connection conn=null;
        String sql="SELECT * FROM customer";
        List<Customer> list =new ArrayList<Customer>();
        try{
            conn=DatabaseHelper.getConnection();
            PreparedStatement stmt=conn.prepareStatement(sql);
            ResultSet rs=stmt.executeQuery();
            while (rs.next()){
                Customer c=new Customer();
                c.setId(rs.getLong("id"));
                c.setName(rs.getString("name"));
                c.setContact(rs.getString("contact"));
                c.setTelephone(rs.getString("telephone"));
                c.setEmail(rs.getString("email"));
                c.setRemark(rs.getString("remark"));

                list.add(c);
            }
        }catch (SQLException e){
            log.error("执行sql失败，SQL为："+sql,e);
        }finally {
            DatabaseHelper.closeConnection(conn);
        }
        return list;
    }

    /**
     * 获取客户列表 方法3
     * 使用DBUtils工具类进行操作
     */
    public List<Customer> getCustomerListThree(String id){
        Connection conn=null;
        String sql="SELECT * FROM customer";
        List<Customer> list =new ArrayList<Customer>();
        try{
            conn=DatabaseHelper.getConnection();
           list=DatabaseHelper.queryEntityList(Customer.class,conn,sql);
        }catch (Exception e){
            log.error("执行sql失败，SQL为："+sql,e);
        }finally {
            DatabaseHelper.closeConnection(conn);
        }
        return list;
    }
    /**
     * 获取客户列表 方法4
     * 使用DBUtils工具类进行操作,并使用线程保存conn数据库连接的方式来调用查询
     * 本方法与getCustomerListThree 的区别在于，方法3需要将conn转给方法去执行
     */
    public List<Customer> getCustomerList(String id){
        String sql="SELECT * FROM customer";
        List<Customer> list = DatabaseHelperThreadLocal.queryEntityList(Customer.class,sql);
        return list;
    }

    /**
     * 获取客户信息
     */
    public Customer getCustomer(long id){
        String sql="SELECT * FROM customer";
        return DatabaseHelperThreadLocal.queryEntity(Customer.class,sql);
    }

    /**
     * 创建客户
     */
    public boolean createCustomer(Map<String,Object> fieldMap){

        return DatabaseHelperThreadLocal.addEntity(Customer.class,fieldMap);
    }
    /**
     * 修改客户
     */
    public boolean updateCustomer(long id,Map<String,Object> fieldMap){
        return DatabaseHelperThreadLocal.updateEntity(Customer.class,id,fieldMap);
    }

    /**
     * 删除客户
     */
    public boolean deleteCustomer(long id){
        return DatabaseHelperThreadLocal.delEntity(Customer.class,id);
    }
}
